﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace stuManage
{
    public partial class stuInf : System.Web.UI.Page
    {
        static List<Student> students = new List<Student>();
        DBHelper helper = new DBHelper();//用于执行数据库操作的对象
        /// <summary>
        /// 页面加载时候去完成的代码
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void Page_Load(object sender, EventArgs e)
        {
            string HTML = "<table class='table table-border'><tr>";
            HTML += "<td>学号</td>";
            HTML += "<td>姓名</td>";
            HTML += "<td>性别</td>";
            HTML += "<td>年龄</td>";
            HTML += "<td>系部</td>";
            HTML += "<td>班级</td>";
            HTML += "</tr><table>";

            //student_inf.InnerHtml = HTML;
        }

        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void Button1_Click(object sender, EventArgs e)
        {
            //TextBox1.CausesValidation = true;
            //TextBox2.CausesValidation = true;
            //TextBox3.CausesValidation = true;
            //1、定义存储学生信息的对象
            Student student = new Student();
            //2、获取信息
            //string id= TextBox1.Text.Trim();
            //string name= TextBox2.Text.Trim();
            //if(id.Length==0)
            //    Response.Write("<script>alert('学号不能为空')</script>");

            student.ID = TextBox1.Text.Trim();
            student.Name = TextBox2.Text.Trim();
            student.Department = DropDownList1.SelectedValue;
            student.ClassName = DropDownList2.SelectedValue;
            student.Age = Convert.ToInt32(TextBox3.Text.Trim());
            if (RadioButton1.Checked)//说明点的是RadioButton1
            {
                student.Sex = RadioButton1.Text;//输出RadioButton1.Text，即男
            }
            if (RadioButton2.Checked)//说明点的是RadioButton1
            {
                student.Sex = RadioButton2.Text;//输出RadioButton1.Text，即男
            }
            //3、在数据库中添加数据
            // students.Add(student);

            string commandString = "insert into student_inf values('{0}','{1}','{2}','{3}','{4}','{5}')";
            commandString = string.Format(commandString, student.ID, student.Name, student.Age, student.Sex, student.Department, student.ClassName);
            helper.ExecuteSQL(commandString);

            //4、显示数据
            ShowStudents("select * from student_inf");
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void Button2_Click(object sender, EventArgs e)
        {
            //TextBox2.CausesValidation = false;
            //TextBox3.CausesValidation = false;
            //TextBox1.CausesValidation = true;

            //1、新建对象
            Student student = new Student();
            //2、获取信息
            student.ID = TextBox1.Text.Trim();

            //3、构造sql语句执行删除
            string commandString = "delete from student_inf where id='"+ student.ID + "'";
           // commandString = string.Format(commandString, student.ID);
            helper.ExecuteSQL(commandString);
            DropDownList1.SelectedIndex = -1;
            DropDownList2.SelectedIndex = -1;
            TextBox1.Text = "";

            TextBox2.Text = "";
            TextBox3.Text = "";

            //4、显示数据
            ShowStudents("select * from student_inf");
        }
        /// <summary>
        /// 查找某个学生
        /// </summary>
        /// <param name="id"></param>
        /// <returns>查找该学生下标</returns>
        int FindStudent(string id)
        {
            int count = -1;
            for(int i = 0; i < students.Count; i++)
            {
                if (students[i].ID == id)
                {
                    count = i;
                    break;
                }
            }
            return count;
        }

        void ShowStudents(string commandString)
        {
            //1、指定要执行的字符串
            SqlDataSource1.SelectCommand = commandString;   //指定查询的命令
            GridView1.DataSourceID = SqlDataSource1.ID;     //将数据源绑定到GridView上
            GridView1.DataBind();   //  绑定控件显示最后结果

        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void Button3_Click(object sender, EventArgs e)
        {
            //TextBox1.CausesValidation = true;
            //TextBox2.CausesValidation = true;
            //TextBox3.CausesValidation = true;

            //1、定义存储学生信息的对象
            Student student = new Student();
            //2、获取信息
            student.ID = TextBox1.Text.Trim();
            student.Name = TextBox2.Text.Trim();
            student.Department = DropDownList1.SelectedValue;
            student.ClassName = DropDownList2.SelectedValue;
            student.Age = Convert.ToInt32(TextBox3.Text.Trim());
            // student.Sex =DropDownList4.SelectedValue;
            if (RadioButton1.Checked)//说明点的是RadioButton1
            {
                student.Sex = RadioButton1.Text;//输出RadioButton1.Text，即男
            }
            if (RadioButton2.Checked)//说明点的是RadioButton1
            {
                student.Sex = RadioButton2.Text;//输出RadioButton1.Text，即男
            }
            //3、构造sql语句执行删除
            string commandString = "update student_inf set name='{0}',age='{1}',sex='{2}',department='{3}',className='{4}' where id={5}";
            commandString = string.Format(commandString, student.Name,student.Age,student.Sex,student.Department,student.ClassName,student.ID);
            helper.ExecuteSQL(commandString);
            //4、显示数据
            ShowStudents("select * from student_inf");
        }

        /// <summary>
        /// 查询某一个
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void Button4_Click(object sender, EventArgs e)
        {
            //TextBox1.CausesValidation = true;
            //TextBox2.CausesValidation = false;
            //TextBox3.CausesValidation = false;

            //1、定义对象
            //Student student = new Student();
            //2、收集信息
            string id= TextBox1.Text.Trim();
            string name =TextBox2.Text.Trim();
            string age= TextBox3.Text.Trim();
            string department = DropDownList1.SelectedValue;
            string className= DropDownList2.SelectedValue;
            if (id.Length > 0)
                ShowStudents("select * from student_inf where id=" + id);
            else if (name.Length > 0)
                ShowStudents("select * from student_inf where name= '" + name + "'");
            else if(age.Length>0)
                ShowStudents("select * from student_inf where age='" + age + "'");
            else
                ShowStudents("select * from student_inf where department='" + department + "' and className='" + className + "'"); ;



        }

        /// <summary>
        /// 查询全部
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void Button5_Click(object sender, EventArgs e)
        {
            //TextBox1.CausesValidation = false;
            //TextBox2.CausesValidation = false;
            //TextBox3.CausesValidation = false;

            //4、显示数据
            ShowStudents("select * from student_inf");

        }

        protected void TextBox1_TextChanged(object sender, EventArgs e)
        {
            DropDownList1.SelectedIndex = -1;
            DropDownList2.SelectedIndex = -1;
            TextBox2.Text = "";
            TextBox3.Text = "";

            string id = TextBox1.Text.Trim();
            string strConn = "server=localhost;port=3306;user id=root;password=lxy1756431734;database=student; pooling=true;";
            MySqlConnection conn = new MySqlConnection(strConn);
            conn.Open();//链接数据库  
            DataSet ds = new DataSet();
            string sql = "select * from student_inf where id = '" + id + "'";
            MySqlCommand com = new MySqlCommand(sql, conn);
            MySqlDataReader dr = com.ExecuteReader();
            if (dr.Read())
            {
                string name = dr["name"].ToString();
                string age = dr["age"].ToString();
                string sex = dr["sex"].ToString();
                string department = dr["department"].ToString();
                string className = dr["className"].ToString();

                TextBox2.Text = name;
                TextBox3.Text = age;
                if (sex == "男")
                {
                    RadioButton2.Checked = false;
                    RadioButton1.Checked = true;

                }
                else
                {
                    RadioButton1.Checked = true;

                    RadioButton2.Checked = true;

                }
                DropDownList1.Items.FindByValue(department).Selected = true;
                DropDownList2.Items.FindByValue(className).Selected = true;

            }

        }
        protected void Button6_Click(object sender, EventArgs e)
        {
            //清除文本框内所有数据
            DropDownList1.SelectedIndex = -1;
            DropDownList2.SelectedIndex = -1;

            TextBox1.Text = "";

            TextBox2.Text = "";
            TextBox3.Text = "";
            ShowStudents("select * from student_inf");


        }
    }
}
